/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package metier.JDBC;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import metier.AnneeEtude;

import metier.ElementListe;
import metier.ExceptionMetier;
import metier.Semestre;

/**
 *
 * @author saad
 */
public class SemestreJDBC extends Semestre {
    /* Attributs prives */

    private final static String ID = "id_semestre"; //Nom de l'attribut idSeance dans la BD
    private final static String NOM = "Nom_Semestre"; 	//Nom de l'attribut service dans la BD
    private final static String ANNEE = "anneeetude"; 		//Nom de l'attribut salle dans la BD
    private final static String DATEDEBUT = "date_debut_Semestre";	//Nom de l'attribut date dans la BD
    private final static String DATEFIN = "date_fin_semestre";
    private final static String NOMTABLE = "SEMESTRE";
    //Requete SQL permettant de mettre a jour l'objet dans la base de donnees
    private final static String updateQuery = "UPDATE " + NOMTABLE + " SET "
            + NOM + " = ?, "
            + ANNEE + " = ?, "
            + DATEDEBUT + " = ?, "
            + DATEFIN + " = ? where "
            + ID + " = ?";
    //Requete SQL permettant de recuperer la liste de toutes les seances de la base de donnees
    private final static String selectQuery = "SELECT * FROM " + NOMTABLE;
    
    private final static String selectAnneeQuery = "SELECT * FROM " + NOMTABLE+" where anneeetude = ?";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String loadQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + ID + " = ?";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String insertQuery = "INSERT INTO " + NOMTABLE + " VALUES(?, ?, ?, ?, ?)";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String deleteQuery = "DELETE FROM " + NOMTABLE + " WHERE " + ID + " = ?";
    private final static String idQuery = "SELECT seq_semestre.nextval FROM dual";

    static String getNomTable() {
        return NOMTABLE;
    }

    static String getIdCL() {
        return ID;
    }

    public SemestreJDBC() {
        super();
    }

    public SemestreJDBC(String nom, AnneeEtude annee,
			Date debut, Date fin){
        super(nom,annee,debut,fin);
    }

    /* Accesseurs */
    protected int createId() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next()) {
                throw new ExceptionMetier("creating new id failed due to non generated id");
            }
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }
    /* (non-Javadoc)
     * @see metier.Seance#delete()
     */

    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(deleteQuery);
            psta.setInt(1, getId());
            psta.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    /* (non-Javadoc)
     * @see metier.Seance#insert()
     */
    @Override
    public void insert() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(insertQuery);
            if (getId() == 0) {
                setId(createId());
            }
            psta.setInt(1, getId());
            psta.setString(2, getNom());
            psta.setDate(3, new java.sql.Date(getDateDebut().getTime()));
            psta.setDate(4, new java.sql.Date(getDateFin().getTime()));
            psta.setInt(5, ((AnneeEtudeJDBC) getAnnee()).getIdAnnee());
          
            psta.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
    }

    /* (non-Javadoc)
     * @see metier.Seance#update()
     */
    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(updateQuery);
            psta.setString(1, getNom());
            psta.setInt(2, ((AnneeEtudeJDBC) getAnnee()).getIdAnnee());
            psta.setDate(3, new java.sql.Date(getDateDebut().getTime()));
            psta.setDate(4, new java.sql.Date(getDateFin().getTime()));
            psta.setInt(5, getId());
            psta.executeQuery();

            //int res =
            psta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }

    @Override
    /* (non-Javadoc)
     * @see metier.Seance#load(int idSeance)
     */
    public void load(int id) throws ExceptionMetier {
        try {
            setId(id);
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(loadQuery);
            psta.setInt(1, getId());
            ResultSet rset = psta.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (!(rset.getInt(ID) == getId())) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            AnneeEtude ae = new AnneeEtudeJDBC();
            ae.load(rset.getInt(ANNEE));
            setNom(rset.getString("nom_semestre"));
            setAnnee(ae);
            setDateDebut(rset.getDate(DATEDEBUT));
            setDateFin(rset.getDate(DATEFIN));
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }
    }

    /* Methodes statiques */
    /**
     * Methode qui retourne la liste des seances dans le systeme de stockage
     * @return ResultSet liste des seances sous forme d'un resultSet
     * @throws ExceptionMetier
     */
    public static ArrayList<ElementListe> select()
            throws ExceptionMetier {
        ArrayList<ElementListe> list = new ArrayList<ElementListe>();


        try {
            ResultSet rset = null;
            Connection con = ConnectionJDBC.getConnection();
            Statement s = con.createStatement();
            rset = s.executeQuery(selectQuery);



            while (rset.next()) {
                AnneeEtude ae = new AnneeEtudeJDBC();
                ae.load(rset.getInt(ANNEE));
                String affichage = rset.getString(NOM) + ", " + ae.getNom();
                list.add(new ElementListe(rset.getInt(ID),affichage));
            }
            return list;


        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);


        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);


        }
    }
    
public static ArrayList<ElementListe> creerListeSemestre(AnneeEtudeJDBC annee) throws ExceptionMetier{
	ArrayList <ElementListe> listeSalle = new ArrayList<ElementListe>();
	try {
	    ResultSet res = null;
	    Connection con = ConnectionJDBC.getConnection();
	    PreparedStatement s = con.prepareStatement(selectAnneeQuery);
	    s.setInt(1,annee.getIdAnnee());
	    System.out.println(annee.getIdAnnee());
	    res = s.executeQuery();
	    
		// Ajoute les salles une par une a la liste
		while (res.next())
		{		
			listeSalle.add(new ElementListe(res.getInt("id_semestre"),res.getString("nom_semestre")));
		}
	    return listeSalle;
	} 
	catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
	catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}

}
